package com.jdbc;

import com.bruidUtils.druidUtils;
import com.pojo.User;
import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 * @Author milian
 * @Date 2021/8/20 0020 19:08
 */
public class title5 {
@Test
public void findAllUser() throws Exception {
    Connection conn = druidUtils.getConnection();
    PreparedStatement pstmt = conn.prepareStatement("select * from user ");
    ResultSet rs = pstmt.executeQuery();
    ArrayList<User> list = new ArrayList<>();
    while (rs.next()){
        String username = rs.getString("username");
        String image = rs.getString("image");
        String signature = rs.getString("signature");
        String nickname = rs.getString("nickname");
        int id = rs.getInt("id");
        User user = new User();
        user.setId(id);
        user.setUsername(username);
        user.setImage(image);
        user.setNickname(nickname);
        user.setNickname(nickname);
        list.add(user);
    }
    if (list.size()!=0){
        for (User user : list) {
            System.out.println(user);
        }
    }
}
@Test
public void findUserById() throws Exception {
    Connection conn = druidUtils.getConnection();
    PreparedStatement pstmt = conn.prepareStatement("select * from user where id=1");
    ResultSet rs = pstmt.executeQuery();
    ArrayList<User> list = new ArrayList<>();
    while (rs.next()){
        String username = rs.getString("username");
        String image = rs.getString("image");
        String signature = rs.getString("signature");
        String nickname = rs.getString("nickname");
        int id = rs.getInt("id");
        User user = new User();
        user.setId(id);
        user.setUsername(username);
        user.setImage(image);
        user.setNickname(nickname);
        user.setNickname(nickname);
        list.add(user);
    }
    if (list.size()!=0){
        for (User user : list) {
            System.out.println(user);
        }
    }else {
        System.out.println("用户不存在！！！");
    }
    pstmt.close();
    conn.close();
}
@Test
public void addUser() throws Exception {
    Connection conn = druidUtils.getConnection();
    PreparedStatement pstmt = conn.prepareStatement("insert into user values ('殷铭亮','1','万般各所是 一切皆圆满','milian','1')");
    int i = pstmt.executeUpdate();
    if (i>0){
        System.out.println("添加成功");
    }else {
        System.out.println("添加失败");
    }
    pstmt.close();
    conn.close();
}
@Test
public void UpdateUser() throws Exception {
    Connection conn = druidUtils.getConnection();
    PreparedStatement pstmt = conn.prepareStatement("update user set id=2 where id=1");
    int i = pstmt.executeUpdate();
    if (i>0){
        System.out.println("修改成功");
    }else {
        System.out.println("修改失败");
    }
    pstmt.close();
    conn.close();
}
@Test
public void DeleteUser() throws Exception {
    Connection conn = druidUtils.getConnection();
    PreparedStatement pstmt = conn.prepareStatement("delete from user where id=2");
    int i = pstmt.executeUpdate();
    if (i>0){
        System.out.println("删除成功");
    }else {
        System.out.println("删除失败");
    }
    pstmt.close();
    conn.close();
}
}
